package moe.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import moe.entity.Pool;
import moe.entity.Post;
import moe.util.Paging;
import moe.util.QueryBuilder;

public class PoolDao extends BaseDao {
	private final static Logger log = Logger.getLogger(PoolDao.class.getName());

	private static final String POOLS_COUNT = "SELECT COUNT(*) FROM pool";
	private static final String POOLS_COUNT_BY_NAME = "SELECT COUNT(*) FROM pool WHERE name LIKE ?";
	private static final String LIST_POOLS =
			"SELECT id, post_count, name, description, user_id, created_at, updated_at, MIN(post_id) AS post_id"
					+ " FROM pool LEFT JOIN pool_posts ON id = pool_id GROUP BY id ORDER BY id DESC LIMIT ?, ?";

	private static final String LIST_POOLS_BY_NAME =
			"SELECT id, post_count, name, description, user_id, created_at, updated_at, MIN(post_id) AS post_id"
					+ " FROM pool LEFT JOIN pool_posts ON id = pool_id WHERE name LIKE ? GROUP BY id ORDER BY id DESC LIMIT ?, ?";

	private static final String GET_FULL_POOL_BY_ID =
			"SELECT * FROM pool LEFT JOIN pool_posts ON id = pool_id WHERE id = ? LIMIT ?, ?";

	public PoolDao() {
		super(BaseDao.ConnType.DEFAULT);
	}

	public PoolDao(ConnType connType) {
		super(connType);
	}

	@SuppressWarnings("unchecked")
	public List<Pool> listPools(final Paging paging) {
		paging.setPageSize(20);
		String sql = LIST_POOLS;
		if (!evaluateTotalCount(paging, POOLS_COUNT)) {
			return Collections.EMPTY_LIST;
		}
		return this.query(sql, new ResultsetHandler<List<Pool>>() {
			public List<Pool> handle(ResultSet rs) throws SQLException {
				List<Pool> result = new ArrayList<Pool>(paging.getPageSize());
				while (rs.next()) {
					Pool pool = new Pool();
					result.add(pool);
					AnnotationColumnMapper.resultSetMap(rs, pool);
					Post post = new Post();
					post.id = rs.getLong("post_id");
					pool.setPosts(Arrays.asList(post));
				}
				return result;
			}
		}, paging.getPagingItemOffset(), paging.getPageSize());
	}

	@SuppressWarnings("unchecked")
	public List<Pool> listPoolsByName(String name, final Paging paging) {
		paging.setPageSize(20);
		String sql = LIST_POOLS_BY_NAME;
		name = "%" + name + "%";
		if (!evaluateTotalCount(paging, POOLS_COUNT_BY_NAME, name)) {
			return Collections.EMPTY_LIST;
		}
		return this.query(sql, new ResultsetHandler<List<Pool>>() {
			public List<Pool> handle(ResultSet rs) throws SQLException {
				List<Pool> result = new ArrayList<Pool>(paging.getPageSize());
				while (rs.next()) {
					Pool pool = new Pool();
					result.add(pool);
					AnnotationColumnMapper.resultSetMap(rs, pool);
					Post post = new Post();
					post.id = rs.getLong("post_id");
					pool.setPosts(Arrays.asList(post));
				}
				return result;
			}
		}, name, paging.getPagingItemOffset(), paging.getPageSize());
	}

	public Pool getPoolById(Long id, final Paging paging) {
		String sql = GET_FULL_POOL_BY_ID;
		evaluateTotalCount(paging, sql, id);
		return this.query(sql, new ResultsetHandler<Pool>() {
			public Pool handle(final ResultSet rs) throws SQLException {
				Pool result = null;
				while (rs.next()) {
					if (result == null) {
						result = new Pool();
						result.setPosts(new ArrayList<Post>(paging.getPageSize()));
						AnnotationColumnMapper.resultSetMap(rs, result);
					}
					final Post post = new Post();
					post.id = rs.getLong("post_id");
					result.getPosts().add(post);
				}
				return result;
			}
		}, id, paging.getPagingItemOffset(), paging.getPageSize());
	}

	public List<Pool> listFullPools(final Paging paging) {
		List<Pool> result = null;
		String sql = "SELECT * FROM pool LIMIT ?, ?";
		if (!evaluateTotalCount(paging, sql)) {
			return Collections.EMPTY_LIST;
		}
		result = this.query(sql, new ResultsetHandler<List<Pool>>() {
			public List<Pool> handle(ResultSet rs) throws SQLException {
				List<Pool> result = new ArrayList<Pool>(paging.getPageSize());
				while (rs.next()) {
					Pool pool = new Pool();
					result.add(pool);
					AnnotationColumnMapper.resultSetMap(rs, pool);
				}
				return result;
			}
		}, paging.getPagingItemOffset(), paging.getPageSize());

		QueryBuilder builder = new QueryBuilder("pool_posts", null);
		int length = result.size();
		Long[] ids = new Long[length];
		Object[] args = new Object[length];
		for (int i = 0; i < length; i++) {
			ids[i] = result.get(i).getId();
			args[i] = QueryBuilder.PLACEHOLDER;
		}
		builder.in("pool_id", args);

		Map<Long, List<Post>> poolPostMap =
				this.query(builder.build(), false, new ResultsetHandler<Map<Long, List<Post>>>() {
					public Map<Long, List<Post>> handle(ResultSet rs) throws SQLException {
						Map<Long, List<Post>> result = new HashMap<Long, List<Post>>();
						while (rs.next()) {
							Long poolId = rs.getLong("pool_id");
							Long postId = rs.getLong("post_id");
							if (!result.containsKey(poolId)) {
								result.put(poolId, new LinkedList<Post>());
							}
							Post post = new Post();
							post.id = postId;
							result.get(poolId).add(post);
						}
						return result;
					}
				}, ids);
		for (Pool pool : result) {
			if (poolPostMap.containsKey(pool.getId())) {
				pool.setPosts(poolPostMap.get(pool.getId()));
			} else {
				pool.setPosts(Collections.EMPTY_LIST);
			}
		}
		return result;
	}

	public void insertPool(Pool pool) {
		if (pool == null) {
			throw new RuntimeException("pool is null");
		}
		List<String> sqls = new LinkedList<String>();
		sqls.add(AnnotationColumnMapper.mappingToInsert("pool", pool));
		String sqlTemplate =
				"INSERT INTO pool_posts (pool_id, post_id) VALUES ({0,number,#}, {1,number,#})";
		MessageFormat formater = new MessageFormat(sqlTemplate);
		for (Post post : pool.getPosts()) {
			sqls.add(formater
					.format(new Long[] { pool.getId(), post.id }, new StringBuffer(), null)
					.toString());
		}
		this.executeBatch(sqls);
	}
}
